package com.apes.scm.masterdata.stock.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.stock.model.DcDistributionRange;
import com.apestech.framework.json.JSONArray;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

public interface DcDistributionRangeRepository extends CustomRepository<DcDistributionRange, String> {


    @Query("select new map(T.id as id, T.FLocationId.id as FLocationId, T.CLocationId.id as CLocationId, T.updateDate as updateDate, " +
            "T.offlineWeights as offlineWeights, T.onlineWeights as onlineWeights, T.safetyStockMark as safetyStockMark, T.createDate as createDate, " +
            " to_char(T.lastUpdatedStamp, 'yyyy-mm-dd hh24:mi:ss.FF3') as lastUpdatedStamp ) from DcDistributionRange T " +
            " where T.lastUpdatedStamp > :times or (T.id > :id and T.lastUpdatedStamp = :times) ")
    List<Map> queryDcDistributionByTs(@Param("times") Date times, Pageable pageable, String id);


    @Query(value = "" +
            " SELECT A.*, " +
            "       DECODE(DEPOT_LEVEL, '000123', '区域中心仓', '000121', '配送中心', '000122', " +
            "               '门店') DEPOT_LEVEL_NAME " +
            "  FROM (SELECT F_LOCATION_ID, C_LOCATION_ID, STORE_ID, " +
            "                (SELECT (SELECT DEPOT_LEVEL " +
            "                             FROM MAS_DEPOT " +
            "                            WHERE ID = J.DEPOT_ID) " +
            "                    FROM MAS_LOCATION J " +
            "                   WHERE ID = A.C_LOCATION_ID) DEPOT_LEVEL " +
            "           FROM (SELECT C_LOCATION_ID, F_LOCATION_ID, " +
            "                        (SELECT (SELECT (SELECT ID FROM MAS_STORE WHERE DEPT_ID = B.DEPT_ID ) FROM MAS_DEPOT B WHERE ID = A.DEPOT_ID) FROM MAS_LOCATION A WHERE ID = C_LOCATION_ID) STORE_ID, " +
            "                         ROW_NUMBER() OVER(PARTITION BY C_LOCATION_ID ORDER BY OFFLINE_WEIGHTS DESC) RAK " +
            "                    FROM MAS_DC_DISTRIBUTION_RANGE MDDR" +
            "                   WHERE C_LOCATION_ID IN ?1) A " +
            "          WHERE RAK = 1) A " +
            " ORDER BY DEPOT_LEVEL ", nativeQuery = true)
    List<Map> safetyStockCalcuQueryDC(List locations);

    @Query(value = "" +
            " SELECT A.*, " +
            "       DECODE(DEPOT_LEVEL, '000123', '区域中心仓', '000121', '配送中心', '000122', " +
            "               '门店') DEPOT_LEVEL_NAME " +
            "  FROM (SELECT F_LOCATION_ID, C_LOCATION_ID, STORE_ID, " +
            "                (SELECT (SELECT DEPOT_LEVEL " +
            "                             FROM MAS_DEPOT " +
            "                            WHERE ID = J.DEPOT_ID) " +
            "                    FROM MAS_LOCATION J " +
            "                   WHERE ID = A.C_LOCATION_ID) DEPOT_LEVEL " +
            "           FROM (SELECT C_LOCATION_ID, F_LOCATION_ID, " +
            "                        (SELECT (SELECT (SELECT ID FROM MAS_STORE WHERE DEPT_ID = B.DEPT_ID ) FROM MAS_DEPOT B WHERE ID = A.DEPOT_ID) FROM MAS_LOCATION A WHERE ID = C_LOCATION_ID) STORE_ID, " +
            "                         ROW_NUMBER() OVER(PARTITION BY C_LOCATION_ID ORDER BY OFFLINE_WEIGHTS DESC) RAK " +
            "                    FROM MAS_DC_DISTRIBUTION_RANGE MDDR" +
            "                   WHERE F_LOCATION_ID = :locationId) A " +
            "          WHERE RAK = 1) A " +
            " ORDER BY DEPOT_LEVEL ", nativeQuery = true)
    List<Map> safetyStockCalcuQueryDC(@Param("locationId") String locationId);

}
